窗口函数
概述
窗口函数（Window Functions）是SQL中一个强大的工具，允许你在查询结果集上执行计算，而不需要将结果集分组或聚合。窗口函数可以让你在同一查询中同时访问多行数据，这在处理排名、累计和移动平均等问题时非常有用。
理论解释
什么是窗口函数？
窗口函数是一种特殊的SQL函数，它对一组行（称为“窗口”）进行计算，并返回一个值。与聚合函数不同，窗口函数不会将行分组为单个输出行；相反，它们为每一行返回一个结果。
窗口函数的语法
窗口函数的基本语法如下：
function_name (expression) OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression]
[ROWS BETWEEN frame_start AND frame_end]
)
function_name: 窗口函数的名称，如ROW_NUMBER,RANK,SUM,AVG等。expression: 要计算的表达式。PARTITION BY: 可选，用于将数据分组。ORDER BY: 可选，用于指定窗口内的排序顺序。ROWS BETWEEN: 可选，用于定义窗口的边界。
常见的窗口函数
ROW_NUMBER(): 为每一行分配一个唯一的序号。RANK(): 为每一行分配一个排名，相同的值会有相同的排名，但会跳过后续的排名。DENSE_RANK(): 类似于RANK()，但不会跳过后续的排名。SUM(),AVG(),MIN(),MAX(): 这些聚合函数也可以作为窗口函数使用。
代码示例
示例1：使用 ROW_NUMBER()
假设我们有一个名为 sales 的表，包含销售数据：
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
product_name VARCHAR(100),
sale_date DATE,
amount DECIMAL(10, 2)
);
INSERT INTO sales (product_name, sale_date, amount) VALUES
('Product A', '2023-01-01', 100.00),
('Product B', '2023-01-02', 200.00),
('Product A', '2023-01-03', 150.00),
('Product B', '2023-01-04', 250.00),
('Product A', '2023-01-05', 300.00);
我们可以使用 ROW_NUMBER() 为每一行分配一个序号：
SELECT
product_name,
sale_date,
amount,
ROW_NUMBER() OVER (ORDER BY sale_date) AS row_num
FROM sales;
输出：
product_name | sale_date | amount | row_num
--------------+------------+--------+---------
Product A | 2023-01-01 | 100.00 | 1
Product B | 2023-01-02 | 200.00 | 2
Product A | 2023-01-03 | 150.00 | 3
Product B | 2023-01-04 | 250.00 | 4
Product A | 2023-01-05 | 300.00 | 5
示例2：使用 RANK() 和 DENSE_RANK()
假设我们有一个名为 scores 的表，包含学生的考试成绩：
CREATE TABLE scores (
student_id INT,
subject VARCHAR(50),
score INT
);
INSERT INTO scores (student_id, subject, score) VALUES
(1, 'Math', 90),
(2, 'Math', 85),
(3, 'Math', 90),
(4, 'Math', 80),
(5, 'Math', 75);
我们可以使用 RANK() 和 DENSE_RANK() 来计算每个学生的排名：
SELECT
student_id,
subject,
score,
RANK() OVER (ORDER BY score DESC) AS rank,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;
输出：
student_id | subject | score | rank | dense_rank
------------+---------+-------+------+------------
1 | Math | 90 | 1 | 1
3 | Math | 90 | 1 | 1
2 | Math | 85 | 3 | 2
4 | Math | 80 | 4 | 3
5 | Math | 75 | 5 | 4
示例3：使用 SUM() 作为窗口函数
我们可以使用 SUM() 来计算累计和：
SELECT
product_name,
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sum
FROM sales;
输出：
product_name | sale_date | amount | cumulative_sum
--------------+------------+--------+---------------
Product A | 2023-01-01 | 100.00 | 100.00
Product B | 2023-01-02 | 200.00 | 300.00
Product A | 2023-01-03 | 150.00 | 450.00
Product B | 2023-01-04 | 250.00 | 700.00
Product A | 2023-01-05 | 300.00 | 1000.00
实践练习
练习1：计算每个产品的累计销售量
使用 SUM() 窗口函数，计算每个产品的累计销售量。
SELECT
product_name,
sale_date,
amount,
SUM(amount) OVER (PARTITION BY product_name ORDER BY sale_date) AS cumulative_sum
FROM sales;
练习2：计算每个学生的排名
使用 RANK() 和 DENSE_RANK() 窗口函数，计算每个学生在不同科目中的排名。
SELECT
student_id,
subject,
score,
RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS dense_rank
FROM scores;
练习3：计算移动平均
使用 AVG() 窗口函数，计算每个产品的3天移动平均销售量。
SELECT
product_name,
sale_date,
amount,
AVG(amount) OVER (
PARTITION BY product_name
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM sales;
总结
窗口函数是SQL中一个非常强大的工具，允许你在查询结果集上执行复杂的计算。通过使用 ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG() 等窗口函数，你可以轻松地处理排名、累计和移动平均等问题。通过实践练习，你可以更好地理解和掌握这些功能。